﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using TarreOA.TarreOAModels;

namespace TarreOA.TarreOADAL
{
    public class DiaryFileUploadMappingtbDAO
    {
        //保存日记的相关附件信息
        public int saveAffixFileInfo(string fileName, string filePath, DateTime uploadTime, int diaryId)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "insert into DiaryFileUploadMappingtb values(@FileName, @FilePath, @uploadTime, @diaryId)";
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@FileName", fileName));
                sqlCmd.Parameters.Add(new SqlParameter("@FilePath", @filePath));
                sqlCmd.Parameters.Add(new SqlParameter("@uploadTime", uploadTime));
                sqlCmd.Parameters.Add(new SqlParameter("@diaryId", diaryId));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }

        //根据所属日记查找附件信息
        public List<DiaryFileUploadMappingtb> findByDiaryId(int did)
        {
            List<DiaryFileUploadMappingtb> list = null;
            SqlConnection sqlConn = null;
            SqlDataReader sqlDr = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "select * from DiaryFileUploadMappingtb where DiaryId = @did";
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@did", did));
                sqlDr = sqlCmd.ExecuteReader();
                if (sqlDr != null && sqlDr.HasRows)
                {
                    list = new List<DiaryFileUploadMappingtb>();
                    UserDiarytbDAO userDiarytbDAO = new UserDiarytbDAO();
                    while (sqlDr.Read())
                    {
                        DiaryFileUploadMappingtb diaryFileUploadMappingtb = new DiaryFileUploadMappingtb();
                        diaryFileUploadMappingtb.FileId = (int)sqlDr["FileId"];
                        diaryFileUploadMappingtb.FileName = sqlDr["FileName"].ToString();
                        diaryFileUploadMappingtb.FilePath = sqlDr["FilePath"].ToString();
                        diaryFileUploadMappingtb.UploadTime = (DateTime)sqlDr["UploadTime"];
                        diaryFileUploadMappingtb.UserDiary = userDiarytbDAO.findByDiaryId((int)sqlDr["DiaryId"]);
                        list.Add(diaryFileUploadMappingtb);
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlDr != null)
                {
                    sqlDr.Close();
                    sqlDr.Dispose();
                }
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return list;
        }

        //根据所属日记删除附件信息
        public int delAffixFileInfoByDiaryId(int did)
        {
            int count = 0;
            SqlConnection sqlConn = null;
            try
            {
                sqlConn = SQLManager.GetConn();
                sqlConn.Open();
                string sql = "delete from DiaryFileUploadMappingtb where DiaryId = @did";
                SqlCommand sqlCmd = new SqlCommand(sql, sqlConn);
                sqlCmd.Parameters.Add(new SqlParameter("@did", did));
                count = sqlCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                if (sqlConn != null && sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                    sqlConn.Dispose();
                }
            }
            return count;
        }
    }
}
